package com.kcs.fxmargin.action;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import com.kcs.common.utils.Constant;
import com.kcs.common.utils.commonUtils;
import com.kcs.fxmargin.action.form.DailyReportActionForm;
import com.kcs.fxmargin.bo.DailyReportBO;
import com.kcs.fxmargin.service.DealsSumService;

public class DailyReportAction extends FXAction {
	private Logger log = Logger.getLogger(Constant.LOG_FX);
	
	private DealsSumService service = new DealsSumService();

	@Override
	public ActionForward process(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		// TODO Auto-generated method stub
		try{

			log.debug("DailyReportAction : Start");			
			
			DailyReportActionForm formBean = (DailyReportActionForm) form;
			if("init".equals(formBean.getAction())){
				formBean.resetExpect();
				formBean.setAsOfDate(commonUtils.formatDate(commonUtils.getCurrentDate(), "dd/MM/yyyy"));
				formBean.setDailyReportList(new ArrayList<DailyReportBO>());
			} else if("search".equals(formBean.getAction())) search(formBean, request);	
			else if ("export".equals(formBean.getAction())) exportDailyReport(formBean, request, response);
			
			formBean.setAction("init");
			
			log.debug("DailyReportAction : End");
			
		}catch(Exception e){
			log.error(e.getMessage());
			return (mapping.findForward("error"));
		}
		
		
		return mapping.findForward("dailyReport");
	}

	public void search(DailyReportActionForm formBean, HttpServletRequest request) throws Exception {
		log.debug("DailyReportAction : search : Start");	

		DailyReportBO bo = new DailyReportBO();
		bo.setCifNo(formBean.getCifNo());
		bo.setAsOfDate(commonUtils.convertStringtoSQLDate(formBean.getAsOfDate(), "dd/MM/yyyy"));
		bo.setStatus(formBean.getStatus());
		
		formBean.setDailyReportList(service.searchDailyReport(bo));
		request.setAttribute(Constant.SHOW_RESULT, Constant.SHOW_RESULT);
		
		if(formBean.getDailyReportList() != null && formBean.getDailyReportList().size() > 0)
			request.setAttribute(Constant.NOT_EMPTY, Constant.NOT_EMPTY);
		
		log.debug("DailyReportAction : search : End");
	}
	
	@SuppressWarnings("deprecation")
	public void exportDailyReport(DailyReportActionForm formBean, HttpServletRequest request, HttpServletResponse response)throws Exception{
		
		log.debug("DailyReportAction : exportDailyReport : Start");
		List<DailyReportBO> dailyReportList = formBean.getDailyReportList();
		ServletOutputStream out	= null;
		HSSFWorkbook workbook 	= null;
		HSSFSheet sheet 		= null;
		HSSFRow row 			= null;
		HSSFCell cell 			= null;
		DailyReportBO bo 		= null;
		HSSFCellStyle style 	= null;
		HSSFFont font 			= null;
		Integer index			= 0;
		String status			= "N".equals(formBean.getStatus())?"Normal":("O".equals(formBean.getStatus())?"Over":"All");
		
		if(dailyReportList != null && dailyReportList.size()>0){
			workbook 	= new HSSFWorkbook();
			sheet 		= workbook.createSheet("Daily Report");
			style 		= workbook.createCellStyle();
			font 		= workbook.createFont();
			
			//-- Header cell style
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style.setFont(font);
			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style.setFillForegroundColor(new HSSFColor.AQUA().getIndex());
			
			row = sheet.createRow(index);
			cell = row.createCell(0, Cell.CELL_TYPE_STRING);
			cell.setCellValue("CIF No. : " + formBean.getCifNo());
			index++;
			
			row = sheet.createRow(index);
			cell = row.createCell(0, Cell.CELL_TYPE_STRING);
			cell.setCellValue("Customer Name : " + formBean.getCustomerName());
			index++;
			
			row = sheet.createRow(index);
			cell = row.createCell(0, Cell.CELL_TYPE_STRING);
			cell.setCellValue("As Of Date : " + formBean.getAsOfDate());
			index++;
			
			row = sheet.createRow(index);
			cell = row.createCell(0, Cell.CELL_TYPE_STRING);
			cell.setCellValue("Status : " + status);
			index++;
			
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 12));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 12));
			sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 12));
			sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 12));
			index++;
						
			row = sheet.createRow(index);
			cell = row.createCell(0, Cell.CELL_TYPE_STRING);
			cell.setCellValue("No.");
			cell.setCellStyle(style);
			cell = row.createCell(1, Cell.CELL_TYPE_STRING);
			cell.setCellValue("CIF No.");
			cell.setCellStyle(style);
			cell = row.createCell(2, Cell.CELL_TYPE_STRING);
			cell.setCellValue("Customer Name");
			cell.setCellStyle(style);
			cell = row.createCell(3, Cell.CELL_TYPE_STRING);
			cell.setCellValue("Cash Balance");
			cell.setCellStyle(style);
			cell = row.createCell(4, Cell.CELL_TYPE_STRING);
			cell.setCellValue("Realized P/L");
			cell.setCellStyle(style);
			cell = row.createCell(5, Cell.CELL_TYPE_STRING);
			cell.setCellValue("Unrealized P/L");
			cell.setCellStyle(style);
			cell = row.createCell(6, Cell.CELL_TYPE_STRING);
			cell.setCellValue("Equity Balance");
			cell.setCellStyle(style);
			cell = row.createCell(7, Cell.CELL_TYPE_STRING);
			cell.setCellValue("IM Required");
			cell.setCellStyle(style);
			cell = row.createCell(8, Cell.CELL_TYPE_STRING);
			cell.setCellValue("Excess");
			cell.setCellStyle(style);
			cell = row.createCell(9, Cell.CELL_TYPE_STRING);
			cell.setCellValue("MM Required");
			cell.setCellStyle(style);
			cell = row.createCell(10, Cell.CELL_TYPE_STRING);
			cell.setCellValue("Margin Call");
			cell.setCellStyle(style);
			cell = row.createCell(11, Cell.CELL_TYPE_STRING);
			cell.setCellValue("Limit (THB)");
			cell.setCellStyle(style);
			cell = row.createCell(12, Cell.CELL_TYPE_STRING);
			cell.setCellValue("Status");
			cell.setCellStyle(style);
			index++;
					
			for(int i = 0 ; i < dailyReportList.size() ; i++){
				bo = dailyReportList.get(i);
				
				row = sheet.createRow(i+index);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				cell = row.createCell(0, Cell.CELL_TYPE_STRING);
				cell.setCellValue(i+1);
				cell.setCellStyle(style);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				cell = row.createCell(1, Cell.CELL_TYPE_STRING);
				cell.setCellValue(bo.getCifNo());
				cell.setCellStyle(style);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
				cell = row.createCell(2, Cell.CELL_TYPE_STRING);
				cell.setCellValue(bo.getCustomerName());
				cell.setCellStyle(style);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
				cell = row.createCell(3, Cell.CELL_TYPE_STRING);
				cell.setCellValue(commonUtils.parseToFormat(bo.getCashBalance() + ""));
				cell.setCellStyle(style);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
				cell = row.createCell(4, Cell.CELL_TYPE_STRING);
				cell.setCellValue(commonUtils.parseToFormat(bo.getRealizedPL() + ""));
				cell.setCellStyle(style);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
				cell = row.createCell(5, Cell.CELL_TYPE_STRING);
				cell.setCellValue(commonUtils.parseToFormat(bo.getUnRealzedPL() + ""));
				cell.setCellStyle(style);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
				cell = row.createCell(6, Cell.CELL_TYPE_STRING);
				cell.setCellValue(commonUtils.parseToFormat(bo.getEquityBalance() + ""));
				cell.setCellStyle(style);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
				cell = row.createCell(7, Cell.CELL_TYPE_STRING);
				cell.setCellValue(commonUtils.parseToFormat(bo.getImRequired() + ""));
				cell.setCellStyle(style);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
				cell = row.createCell(8, Cell.CELL_TYPE_STRING);
				cell.setCellValue(commonUtils.parseToFormat(bo.getExcess() + ""));
				cell.setCellStyle(style);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
				cell = row.createCell(9, Cell.CELL_TYPE_STRING);
				cell.setCellValue(commonUtils.parseToFormat(bo.getMmRequired() + ""));
				cell.setCellStyle(style);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
				cell = row.createCell(10, Cell.CELL_TYPE_STRING);
				cell.setCellValue(commonUtils.parseToFormat(bo.getMarginCall() + ""));
				cell.setCellStyle(style);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
				cell = row.createCell(11, Cell.CELL_TYPE_STRING);
				cell.setCellValue(commonUtils.parseToFormat(bo.getLimit() + ""));
				cell.setCellStyle(style);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				cell = row.createCell(12, Cell.CELL_TYPE_STRING);
				cell.setCellValue(bo.getStatus());
				cell.setCellStyle(style);

			}
			
			HSSFFooter footer= sheet.getFooter();
			footer.setLeft("Print By : " + formBean.getUserLoginName());
			footer.setRight(commonUtils.formatDate(new Date(), commonUtils.ddmmyyyyhhmmsswithslash));
			
			java.util.Iterator<HSSFCell> it = row.cellIterator(); 
		    while(it.hasNext()){ 
			         HSSFCell cellTmp = it.next(); 
			         sheet.autoSizeColumn((short)cellTmp.getCellNum(),true); 
			 }
			
		    sheet.getPrintSetup().setLandscape(true);
		    sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
		    
			response.setHeader("Pragma", "no-cache");
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=" + Constant.EXCEL_DAILY_REPORT_FILENAME);
			
			out = response.getOutputStream();
			workbook.write(out);
			//out.flush();
			out.close();
			request.setAttribute(Constant.SHOW_RESULT, Constant.SHOW_RESULT);
			
			log.debug("Export excel successfully.");			

			log.debug("DailyReportAction : exportDailyReport : End");
		}else{
			throw new Exception("Cannot export Excel: Data error.");
		}
		 
	}
	
}
